
import pandas as pd
import os
from sqlalchemy import create_engine, text
import re


# 字段映射关系 - 只保留数据库中实际存在的字段
COLUMN_MAPPING = {
    '平台': 'platform',
    '主订单编号': 'main_order_no',
    '子订单编号': 'sub_order_no',
    '选购商品': 'product_name',
    '商品规格': 'product_spec',
    '商品数量': 'product_quantity',
    '商品ID': 'product_id',
    '商家编码': 'merchant_code',
    '商品单价': 'product_unit_price',
    '订单应付金额': 'order_amount',
    '优惠总金额': 'total_discount',
    '平台优惠': 'platform_discount',
    '商家优惠': 'merchant_discount',
    '商家改价': 'merchant_price_change',
    '支付优惠': 'payment_discount',
    '红包抵扣': 'red_packet_deduction',
    '支付方式': 'payment_method',
    '省': 'province',
    '市': 'city',
    '区': 'district',
    '街道': 'street',
    '详细地址': 'detailed_address',
    '是否修改过地址': 'is_address_modified',
    '买家留言': 'buyer_message',
    '订单提交时间': 'order_submit_time',
    '旗帜颜色': 'flag_color',
    '商家备注': 'merchant_remark',
    '订单完成时间': 'order_complete_time',
    '支付完成时间': 'payment_time',
    'APP渠道': 'app_channel',
    '流量来源': 'traffic_source',
    '订单状态': 'order_status',
    '承诺发货时间': 'promised_ship_time',
    '订单类型': 'order_type',
    '达人ID': 'influencer_id',
    '达人昵称': 'influencer_nickname',
    '所属门店ID': 'store_id',
    '售后状态': 'after_sale_status',
    '取消原因': 'cancel_reason',
    '广告渠道': 'ad_channel',
    '流量类型': 'traffic_type',
    '流量体裁': 'traffic_genre',
    '流量渠道': 'traffic_channel',
    '发货时间': 'ship_time',
    '降价类优惠': 'price_reduction_discount',
    '平台实际承担优惠金额': 'platform_actual_discount',
    '商家实际承担优惠金额': 'merchant_actual_discount',
    '达人实际承担优惠金额': 'influencer_actual_discount',
    '预计送达时间': 'estimated_arrival_time',
    '是否平台仓自流转': 'is_platform_warehouse'
}


def extract_number(value):
    """从文本中提取数字"""
    if pd.isna(value) or value in ['', '-', None]:
        return 0.0
    if isinstance(value, (int, float)):
        return float(value)

    match = re.search(r'[-+]?\d*\.?\d+', str(value))
    return float(match.group()) if match else 0.0


def main():
    # 1. 读取Excel文件
    files = [f'抖音{i}.xlsx' for i in range(1, 5)]
    dfs = []

    for file in files:
        file_path = fr'G:\工作\每日\{file}'
        if os.path.exists(file_path):
            df = pd.read_excel(file_path)
            df['平台'] = file.replace('.xlsx', '')
            dfs.append(df)
            print(f"读取: {file} - {len(df)} 条")

    combined = pd.concat(dfs, ignore_index=True)
    print(f"总记录: {len(combined)}")

    # 2. 重命名列
    combined = combined.rename(columns=COLUMN_MAPPING)

    # 3. 只保留数据库中存在的字段
    # 获取数据库中的实际字段列表
    engine = create_engine('mysql+pymysql://root:hui123456@localhost/wh')
    with engine.connect() as conn:
        result = conn.execute(text("SHOW COLUMNS FROM douyin_orders"))
        db_columns = [row[0] for row in result]

    print(f"数据库字段数: {len(db_columns)}")

    # 只保留在数据库中也存在的字段
    available_columns = [col for col in COLUMN_MAPPING.values() if col in db_columns]
    combined = combined[available_columns]

    print(f"可用字段: {len(available_columns)}")

    # 4. 数据清洗
    # 时间字段
    time_cols = ['order_submit_time', 'order_complete_time', 'payment_time',
                 'promised_ship_time', 'ship_time', 'estimated_arrival_time']
    for col in time_cols:
        if col in combined.columns:
            combined[col] = pd.to_datetime(combined[col], errors='coerce')

    # 含文本的金额字段
    text_amount_cols = ['platform_discount', 'merchant_discount', 'influencer_discount']
    for col in text_amount_cols:
        if col in combined.columns:
            combined[col] = combined[col].apply(extract_number)

    # 纯数字字段
    number_cols = ['product_quantity', 'product_unit_price', 'order_amount', 'shipping_fee',
                   'total_discount', 'merchant_price_change', 'payment_discount',
                   'red_packet_deduction', 'handling_fee', 'price_reduction_discount',
                   'platform_actual_discount', 'merchant_actual_discount', 'influencer_actual_discount']
    for col in number_cols:
        if col in combined.columns:
            if col == 'product_quantity':
                combined[col] = pd.to_numeric(combined[col], errors='coerce').fillna(0).astype(int)
            else:
                combined[col] = pd.to_numeric(combined[col], errors='coerce').fillna(0.0)

    # 文本字段
    for col in combined.columns:
        if combined[col].dtype == 'object':
            combined[col] = combined[col].astype(str).replace(['nan', 'None', '-', ''], None)

    # 5. 去重
    combined = combined.drop_duplicates(subset=['sub_order_no'], keep='first')
    print(f"去重后: {len(combined)} 条")

    # 6. 插入数据库
    combined.to_sql('douyin_orders', con=engine, if_exists='append', index=False, chunksize=500)
    print("入库成功！")

    # 7. 保存Excel
    combined.to_excel(r'H:\pycharmproject\需求\processed.xlsx', index=False)
    print("Excel保存成功！")


if __name__ == "__main__":
    main()
